	
/*
processing industries: c
*/
tokenize c 
local k 1
foreach s in M { 
	//MANUFACTURING SECTOR:
*create microregion data files from Excel sheets
tempfile t2003 t2004 t2005 t2006 t2007 t2008 t2009 t2010
	//RAIS employment and wage data for manufacturing
	forv y=2003/2010{
		import delimited using "$rais\MR-level\rais-`y'-microregions-sections.csv", varnames(1) clear
		keep averagemonthlywage totalestablishments totaljobs year braid cnaeid
		rename braid id
		keep if cnaeid=="``k''"
		save `t`y'', replace
		if `y'==2010{
			}
		else{
			clear
		}
	}
	forv y=2003/2009{
		append using `t`y''
	}
	sort id year
	drop cnaeid
	*change all letters in id variable to upper case for later merge:
	replace id= upper(id)
	save "$data\MR_rais_`s'_03-10.dta", replace
	
		//national level
		tempfile t2003 t2004 t2005 t2006 t2007 t2008 t2009 t2010
	clear
	forv y=2003/2010{
		import delimited using "$rais\National level\rais-`y'-sections.csv", varnames(1) clear
		keep year cnaeid nominalwagegrowth1year nominalemployeegrowth1year
		keep if cnaeid=="``k''"
		save `t`y'', replace
		if `y'==2010{
			}
		else{
			clear
		}
	}
	forv y=2003/2009{
		append using `t`y''
	}
	sort year
	save "$data\BRAZIL_rais_`s'_03-10.dta", replace
	
		//regional level
		tempfile t2003 t2004 t2005 t2006 t2007 t2008 t2009 t2010
	clear
	forv y=2003/2010{
		import delimited using "$rais\Region level\rais-`y'-regions-sections.csv", varnames(1) clear
		keep year braid cnaeid nominalwagegrowth1year nominalemployeegrowth1year
		keep if cnaeid=="``k''"
		foreach v in nominalwagegrowth1year nominalemployeegrowth1year{
			rename `v' MR`v'
		}
		drop cnaeid
		save `t`y''
		if `y'==2010{
			}
		else{
			clear
		}
	}
	forv y=2003/2009{
		append using `t`y''
	}
	rename braid region
	sort region year
	save "$data\REGION_rais_`s'_03-10.dta", replace

		// Change identifier of microregiao for merging:
	clear
	import excel using "$rais\RAIS_MRcodes.xlsx", first
	rename IDIBGE micregion
		*remove one 0 in the middle of the MR code for later merge:
	tostring micregion, replace
	replace micregion=regexr(micregion,"0","")
	destring micregion, replace
	ren ID id
	sort micregion 
	drop C
	merge 1:m id using "$data\MR_rais_`s'_03-10.dta", nogen keep(3)
		tempfile xx
	save `xx', replace
	keep if micregion==52018 | micregion==29019
	collapse (sum ) totaljobs (mean) averagemonthlywage , by(year micregion)
	tempfile x2
	save `x2', replace
	use `xx', clear
	drop if micregion==52018 | micregion==29019
	append using `x2'
	save "$data\MR_rais_`s'_03-10.dta", replace

	drop id 
	merge m:1 year using "$data\BRAZIL_rais_`s'_03-10.dta", nogen keep (3) // merge with national sector-specific data
	merge m:1 micregion using "$data\MR_allcodes.dta", nogen keep(3) //get all IBGE codes for region and state
	merge m:1 region year using "$data\REGION_rais_`s'_03-10.dta", nogen keep(1 3) //merge with regional sector-specific data
	merge 1:1 micregion year using "$data\MR_rais_02-10.dta", nogen keep(2 3)
	sort micregion year
	
	cap drop age_avg totalestablishments
	replace totaljobs=0 if totaljobs==. & numemp!=.

tempfile main
save `main'
use "$data\MR_allcodes", clear
g id = _n
keep id micregion
merge 1:m micregion using `main', nogen keep(3)
	
save "MR_sector_`s'", replace	
local ++k
}

foreach s in M A C E{
use "MR_sector_`s'", clear	
	forv y=2003/2010{
		g `s'empshare`y'_ = totaljobs/numemp if year==`y'
		bysort micregion: egen `s'empshare`y' = total(`s'empshare`y'_)
		drop `s'empshare`y'_
		bysort micregion: replace `s'empshare`y' = totaljobs/numemp if `s'empshare`y'==0 & year==`y'
	}
	
	xtset micregion year
	tempfile main1
	save `main1'

**	*national wage growth excluding microregion of observation:
keep id year averagemonthlywage totaljobs

g `s'avgwage = .
g `s'avgjobs = .
tempfile mrs
save `mrs'
forv m=1/558{
	use `mrs', clear
	tempfile t`m'
	drop if id==`m' 
	collapse (mean) averagemonthlywage totaljobs, by(year)
	ren averagemonthlywage avw
	ren totaljobs tjo
	g id = `m'
	merge 1:1 id year using `mrs', nogen keep(2 3)
	replace `s'avgwage = avw if id==`m'
	replace `s'avgjobs = tjo if id==`m'
save `mrs', replace
}
drop avw tjo
merge 1:1 id year using `main1', nogen keep(3)
tempfile main2
save `main2'
	
** at regional level:
keep id region year averagemonthlywage totaljobs

g `s'Ravgwage = .
g `s'Ravgjobs = .
tempfile mrs
save `mrs'
forv m=1/64{
	use `mrs', clear
	tempfile t`m'
	drop if id==`m' & region!=1
	collapse (mean) averagemonthlywage totaljobs, by(year)
	ren averagemonthlywage avw
	ren totaljobs tjo
	g id = `m'
	merge 1:1 id year using `mrs', nogen keep(2 3)
		replace `s'Ravgwage = avw if id==`m'
		replace `s'Ravgjobs = tjo if id==`m'
save `mrs', replace
}
forv m=65/252{
	use `mrs', clear
	tempfile t`m'
	drop if id==`m' & region!=2
	collapse (mean) averagemonthlywage totaljobs, by(year)
	ren averagemonthlywage avw
	ren totaljobs tjo
	g id = `m'
	merge 1:1 id year using `mrs', nogen keep(2 3)
		replace `s'Ravgwage = avw if id==`m'
		replace `s'Ravgjobs = tjo if id==`m'
save `mrs', replace
}
forv m=253/412{
	use `mrs', clear
	tempfile t`m'
	drop if id==`m' & region!=3
	collapse (mean) averagemonthlywage totaljobs, by(year)
	ren averagemonthlywage avw
	ren totaljobs tjo
	g id = `m'
	merge 1:1 id year using `mrs', nogen keep(2 3)
		replace `s'Ravgwage = avw if id==`m'
		replace `s'Ravgjobs = tjo if id==`m'
save `mrs', replace
}
forv m=413/506{
	use `mrs', clear
	tempfile t`m'
	drop if id==`m' & region!=4
	collapse (mean) averagemonthlywage totaljobs, by(year)
	ren averagemonthlywage avw
	ren totaljobs tjo
	g id = `m'
	merge 1:1 id year using `mrs', nogen keep(2 3)
		replace `s'Ravgwage = avw if id==`m'
		replace `s'Ravgjobs = tjo if id==`m'
save `mrs', replace
}
forv m=507/558{
	use `mrs', clear
	tempfile t`m'
	drop if id==`m' & region!=5
	collapse (mean) averagemonthlywage totaljobs, by(year)
	ren averagemonthlywage avw
	ren totaljobs tjo
	g id = `m'
	merge 1:1 id year using `mrs', nogen keep(2 3)
		replace `s'Ravgwage = avw if id==`m'
		replace `s'Ravgjobs = tjo if id==`m'
	save `mrs', replace
}
drop avw tjo
merge 1:1 id year using `main2', nogen keep(3)
	
*IVs: interaction of initial employment share and wage / employment growth
	g `s'IV = `s'empshare2003*`s'avgwage
	g `s'IVE = `s'empshare2003*`s'avgjobs
		// at regional level:
	g `s'RIV = `s'empshare2003*`s'Ravgwage
	g `s'RIVE = `s'empshare2003*`s'Ravgjobs

	keep `s'empshare20* `s'IV `s'IVE `s'RIV `s'RIVE year micregion id ///
			`s'Ravgwage `s'Ravgjobs `s'avgwage `s'avgjobs

	reshape wide `s'Ravgwage `s'Ravgjobs `s'avgwage `s'avgjobs ///
			`s'IV `s'IVE `s'RIV `s'RIVE , i(micregion) j(year)
		
	save "$data\MR_sector_`s'_03-10_p2", replace
	
*** For ORIGIN:
	local c 1
forv y=2003/2009{
	use "$data\MR_sector_`s'_03-10_p2", clear
	sort micregion
	rename micregion orig_mr
	keep orig_mr `s'IV* `s'RIV* 
				
	rename `s'IV`y' o_`s'IV_`y'
	rename `s'IVE`y' o_`s'IVE_`y'
	rename `s'RIV`y' o_`s'RIV_`y'
	rename `s'RIVE`y' o_`s'RIVE_`y'

	tempfile temp1
	save `temp1'
	if `c'==1 {
		tempfile temp2
		save `temp2', replace
		}
	else if `c'>1{
		use `temp2',clear
		merge m:1 orig_mr using `temp1', keep(1 3) nogen
		tempfile temp2
		save `temp2', replace
	}
		local ++c
	}
keep orig_mr o_*
	save "$data\MR_`s'IV_origin_p2", replace
	
}
	